from typing import Optional

import pymysql


def create_lottery_table(connection: pymysql.connections.Connection, table_name: str):
    """
    创建彩种专用表（带自增ID主键）
    :param connection:
    :param table_name:
    :return:
    """
    with connection.cursor() as cursor:
        cursor.execute(
            f"""
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gmt_bet_no VARCHAR(50) NOT NULL,
                open_number VARCHAR(100) NOT NULL,
                open_time DATETIME NOT NULL,
                fetch_time DATETIME NOT NULL,
                UNIQUE KEY uk_gmt_bet_no (gmt_bet_no),
                INDEX idx_open_time (open_time)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """
        )
    connection.commit()


def get_last_bet_no(connection: pymysql.connections.Connection, lottery_code: str) -> Optional[str]:
    """
    获取最后记录的期号
    :param connection:
    :param lottery_code:
    :return:
    """
    with connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT last_bet_no
            FROM lottery_meta
            WHERE lottery_code = %s
            """,
            (lottery_code,),
        )
        result = cursor.fetchone()
        return result["last_bet_no"] if result and result["last_bet_no"] else None


def update_last_bet_no(connection: pymysql.connections.Connection, lottery_code: str, bet_no: str):
    """
    更新最后记录的期号
    :param connection:
    :param lottery_code:
    :param bet_no:
    :return:
    """
    with connection.cursor() as cursor:
        cursor.execute(
            """
            UPDATE lottery_meta
            SET last_bet_no = %s,
                last_request_time = NOW()
            WHERE lottery_code = %s
            """,
            (bet_no, lottery_code),
        )
    connection.commit()


def get_lottery_table_name(connection: pymysql.connections.Connection, lottery_code: str) -> Optional[dict]:
    with connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT table_name
            FROM lottery_meta
            WHERE lottery_code = %s
            """,
            (lottery_code,),
        )
        result = cursor.fetchone()
        return result


def insert_lottery_data(
    cursor: pymysql.cursors.Cursor,
    table_name: str,
    gmt_bet_no: str,
    open_number: str,
    open_time: str,
    fetch_time: str
):
    cursor.execute(
        f"""
            INSERT INTO `{table_name}` (gmt_bet_no, open_number, open_time, fetch_time)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                open_number = VALUES(open_number),
                fetch_time = VALUES(fetch_time)
        """,
        (gmt_bet_no, open_number, open_time, fetch_time),
    )
    return cursor.rowcount
